# ----------------------------------------------------------
# This script downloads the Google Solar Project Data 
# ----------------------------------------------------------

# Loading Packages 
library(pacman)
p_load(bigrquery, DBI, dplyr, here)

# Loading my billing id for GCE 
billing_id = Sys.getenv("GCE_DEFAULT_PROJECT_ID")
bq_auth(path = Sys.getenv("GCE_AUTH_FILE"))

# Connecting to database
bq_con =
  dbConnect(
    bigrquery::bigquery(),
    project = "bigquery-public-data",
    dataset = "sunroof_solar",
    billing = billing_id
  )

# Checking tables available 
dbListTables(bq_con)

# Getting data
solar_census_tract = tbl(bq_con, "solar_potential_by_censustract") |> collect()
solar_zip_code = tbl(bq_con, "solar_potential_by_postal_code") |> collect()

# Saving the results 
fwrite(
  solar_census_tract,
  file = here("Data/google-project-sunroof/project-sunroof-census_tract.csv")
)
fwrite(
  solar_zip_code,
  file = here("Data/google-project-sunroof/project-sunroof-postal_code.csv")
)